package il.ac.tau.dbcourse.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import il.ac.tau.dbcourse.db.annotations.SQLTable;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

@SQLTable(table = "USER_TRACK")
public class UserTrack extends AbsPersistence {

	private String path;
	private Track track;
	private String title;
	private String albumName;
	private String artistName;

	public static List<UserTrack> getAll(Connection conn) throws DBException {
		ArrayList<UserTrack> results = new ArrayList<UserTrack>();

		String tableName = PersistenceManager.getTableName(UserTrack.class);
		PreparedStatement stmt;
		try {
			stmt = conn.prepareStatement("select ID from " + tableName);

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				results.add(new UserTrack(conn, rs.getLong("ID")));
			}
		} catch (SQLException e) {
			throw new DBException("Can't fetch all list");
		}
		return results;
	}

	public static List<UserTrack> getAllMatched(Connection conn)
			throws DBException {
		ArrayList<UserTrack> results = new ArrayList<UserTrack>();

		String tableName = PersistenceManager.getTableName(UserTrack.class);
		PreparedStatement stmt;
		try {
			stmt = conn.prepareStatement("select ID from " + tableName
					+ " WHERE TRACK_ID IS NOT NULL");

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				results.add(new UserTrack(conn, rs.getLong("ID")));
			}
		} catch (SQLException e) {
			throw new DBException("Can't fetch all list");
		}
		return results;
	}

	public UserTrack(String path) {
		tableName = PersistenceManager.getTableName(this.getClass());
		this.path = path;
		setDirty(true);
	}

	public UserTrack(Connection conn, Long id) {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			tableName = PersistenceManager.getTableName(this.getClass());
			stmt = conn.prepareStatement("select * from " + tableName
					+ " where ID=?");
			stmt.setLong(1, id);

			rs = stmt.executeQuery();
			if (!rs.next())
				throw new SQLException();
			path = rs.getString("PATH");
			Long trackId = rs.getLong("TRACK_ID");
			if (trackId != null)
				track = new Track(conn, trackId);
			else
				track = null;

			title = rs.getString("TITLE");
			albumName = rs.getString("ALBUM_NAME");
			artistName = rs.getString("ARTIST_NAME");
			this.id = id;
			setDirty(false);
			rs.close();
			stmt.close();
			rs = null;
			stmt = null;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (rs != null)
					rs.close();
			} catch (Exception e) {
			}
		}
	}

	public void setPath(String path) throws DBException {
		if (path == null || path.compareTo("") == 0)
			throw new DBException();
		if (path.compareTo(this.path) == 0)
			return;
		setDirty(true);
		this.path = path;
	}

	public void setArtistName(String artistName) {
		if (artistName == null)
			artistName = "";
		setDirty(true);
		this.artistName = artistName;
	}

	public String getArtistName() {
		return artistName;
	}

	public void setTitle(String title) {
		if (title == null)
			title = "";
		setDirty(true);
		this.title = title;
	}

	public String getTitle() {
		return title;
	}

	public void setAlbumName(String albumName) {
		if (albumName == null)
			albumName = "";
		setDirty(true);
		this.albumName = albumName;
	}

	public String getAlbumName() {
		return albumName;
	}

	public String getPath() {
		return this.path;
	}

	public void setTrack(Track track) {
		setDirty(true);
		this.track = track;
	}

	public void setTrack(Connection conn, Long id) {
		setDirty(true);
		if (id == null)
			this.track = null;
		else
			this.track = new Track(conn, id);

	}

	public Track getTrack() {
		return this.track;
	}

	@Override
	protected Long saveImpl(Connection conn) {
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			if (track != null && track.isDirty())
				track.saveImpl(conn);

			if (id == null) {
				stmt = conn
						.prepareStatement(
								"insert into "
										+ tableName
										+ "(PATH,TRACK_ID,TITLE,ALBUM_NAME,ARTIST_NAME) "
										+ "values (?,?,?,?,?)",
								Statement.RETURN_GENERATED_KEYS);
			} else {
				stmt = conn
						.prepareStatement("update "
								+ tableName
								+ " set "
								+ "PATH = ?,TRACK_ID= ?, TITLE = ?, ALBUM_NAME = ?, ARTIST_NAME = ? "
								+ "where ID=?");
			}

			stmt.setString(1, path);
			stmt.setObject(2, (track != null) ? track.getId() : null,
					Types.INTEGER);
			stmt.setObject(3, title, Types.VARCHAR);
			stmt.setObject(4, albumName, Types.VARCHAR);
			stmt.setObject(5, artistName, Types.VARCHAR);

			if (id != null)
				stmt.setLong(6, id);

			stmt.executeUpdate();

			if (id == null) {
				rs = stmt.getGeneratedKeys();
				if (rs.next()) {
					String rowId = rs.getString(1);
					id = PersistenceManager.getId(conn, this.getClass(), rowId);
				}
			}

			setDirty(false);
			return id;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (stmt != null)
				try {
					stmt.close();
				} catch (SQLException e) {
				}
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
				}
		}
		return null;
	}

}
